package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.hibernate.SQLQuery;
import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.tag.vo.datatable.SortDirection;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;


import yongxin.entity.annealer.AnnealerEntity;
import yongxin.entity.check.ContractEntity;
import yongxin.entity.check.ContractProcutEntity;
import yongxin.entity.processreport.ProcessReportEntity;
import yongxin.entity.temp_record.TempRecordEntity;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 采购申请单 查询  
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/porequestController")
public class POrequestController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(POrequestController.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 
	 * 采购申请单 查询 列表
	 * 
	 * @return
	 */
	@RequestMapping(params = "porequestList")
	public ModelAndView contractList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/porequestList");

	}

	/**
	 * easyui 采购申请单 查询
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) throws ClassNotFoundException
	{
		
		
		
/*		try
		{
			String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 加载JDBC驱动
			String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=YongXinDB"; // 连接服务器和数据库
			String userName = "sa"; // 默认用户名
			String userPwd = "mingliangyou"; // 密码

			// 加载驱动
			Class.forName(driverName);
			// 获得连接
			Connection conn = DriverManager.getConnection(dbURL, userName, userPwd);

			// 创建存储过程的对象
			CallableStatement c = conn.prepareCall("{call proc_getList}");

		//	c.setString(1, "p10");

			ResultSet rs = c.executeQuery();

			while (rs.next())
			{
				String stuid = rs.getString("FProp01");
				String name = rs.getString("FNumber");
				BigDecimal address = rs.getBigDecimal("FOutQty");

				System.out.println(stuid + " --  " + name + " --  " + address);
			}
			c.close();

		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
		  
		   
		
/*		String proc = "{call proc_getList}";
		SQLQuery query = systemService.getSession().createSQLQuery(proc);
	//	query.setParameter(0,"06");
		List result = query.list();
		*/
		
		
		
		
		
		
		
		

		String sql = " select v1.Fdate AS Fdate,v1.FBillNo AS FBillNo,t11.FNumber AS FFullNumber,t11.Fname AS FItemName, ";
		sql += " isnull(t11.Fmodel,'') AS FItemModel,t14.FName AS FUnitIDName,Convert(decimal(18,4),u1.Fauxqty) AS Fauxqty, ";
		sql += " t4.FName AS FDeptIDName,u1.FFetchTime AS FFetchTime,t10.FName AS FuserName, ";
		sql += " t8.FName AS FRequesterIDName,u1.FEntrySelfP0126 AS FUse ";
		sql += " from RemoteServer.AIS2009.dbo.POrequest v1 INNER JOIN RemoteServer.AIS2009.dbo.POrequestEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0  ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Department t4 ON     v1.FDeptID = t4.FItemID   AND t4.FItemID <>0 ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Emp t8 ON     v1.FRequesterID = t8.FItemID   AND t8.FItemID <>0 ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_User t10 ON     v1.FBillerID = t10.FUserID   AND t10.FUserID <>0 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t11 ON     u1.FItemID = t11.FItemID   AND t11.FItemID <>0 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_MeasureUnit t14 ON     u1.FUnitID = t14.FItemID   AND t14.FItemID <>0 ";
		sql += " where 1=1 AND  ((v1.FCheckerID>0) and  v1.FCancellation = 0)  ";

		String Fdate_begin = request.getParameter("Fdate_begin");
		String Fdate_end = request.getParameter("Fdate_end");
		String FBillNo = request.getParameter("FBillNo");
		String FFullNumber = request.getParameter("FFullNumber");
		String FItemName = request.getParameter("FItemName");
		String FItemModel = request.getParameter("FItemModel");
		String FDeptIDName = request.getParameter("FDeptIDName");

		int num = 0;
		if (StringUtil.isNotEmpty(Fdate_begin) && StringUtil.isNotEmpty(Fdate_end))
		{
			num++;
			sql += " and v1.Fdate between " + "'" + Fdate_begin + "'" + " and " + "'" + Fdate_end + "'";
		}

		if (StringUtil.isNotEmpty(FBillNo))
		{
			num++;
			sql += " and v1.FBillNo like '%" + FBillNo + "%'";
		}
		if (StringUtil.isNotEmpty(FFullNumber))
		{
			num++;
			sql += " and tt11.FNumber like " + "'%" + FFullNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			num++;
			sql += " and t11.Fname like " + "'%" + FItemName + "%'";
		}
		if (StringUtil.isNotEmpty(FItemModel))
		{
			num++;
			sql += " and t11.FModel = " + "'" + FItemModel + "'";
		}

		if (StringUtil.isNotEmpty(FDeptIDName))
		{
			num++;
			sql += " and t4.FName  = " + "'" + FDeptIDName + "'";
		}

		// 排序
		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){

					sql += " order by " + orderField + dataGrid.getOrder();

			}
		}

		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		TagUtil.datagrid(response, dataGrid);
	}
	
	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export")
	public void export(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
	     
		String sql = " select Convert(varchar(50),v1.Fdate,23) AS Fdate,v1.FBillNo AS FBillNo,t11.FNumber AS FFullNumber,t11.Fname AS FItemName, ";
		sql += " isnull(t11.Fmodel,'') AS FItemModel,t14.FName AS FUnitIDName,Convert(decimal(18,4),u1.Fauxqty) AS Fauxqty, ";
		sql += " t4.FName AS FDeptIDName,Convert(varchar(50),u1.FFetchTime,23)  AS FFetchTime,t10.FName AS FuserName, ";
		sql += " t8.FName AS FRequesterIDName,u1.FEntrySelfP0126 AS FUse ";
		sql += " from RemoteServer.AIS2009.dbo.POrequest v1 INNER JOIN RemoteServer.AIS2009.dbo.POrequestEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0  ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Department t4 ON     v1.FDeptID = t4.FItemID   AND t4.FItemID <>0 ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Emp t8 ON     v1.FRequesterID = t8.FItemID   AND t8.FItemID <>0 ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_User t10 ON     v1.FBillerID = t10.FUserID   AND t10.FUserID <>0 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t11 ON     u1.FItemID = t11.FItemID   AND t11.FItemID <>0 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_MeasureUnit t14 ON     u1.FUnitID = t14.FItemID   AND t14.FItemID <>0 ";
		sql += " where 1=1 AND  ((v1.FCheckerID>0) and  v1.FCancellation = 0)  ";

		String Fdate_begin = request.getParameter("Fdate_begin");
		String Fdate_end = request.getParameter("Fdate_end");
		String FBillNo = request.getParameter("FBillNo");
		String FFullNumber = request.getParameter("FFullNumber");
		String FItemName = request.getParameter("FItemName");
		String FItemModel = request.getParameter("FItemModel");
		String FDeptIDName = request.getParameter("FDeptIDName");

		int num = 0;
		if (StringUtil.isNotEmpty(Fdate_begin) && StringUtil.isNotEmpty(Fdate_end))
		{
			num++;
			sql += " and v1.Fdate between " + "'" + Fdate_begin + "'" + " and " + "'" + Fdate_end + "'";
		}

		if (StringUtil.isNotEmpty(FBillNo))
		{
			num++;
			sql += " and v1.FBillNo like '%" + FBillNo + "%'";
		}
		if (StringUtil.isNotEmpty(FFullNumber))
		{
			num++;
			sql += " and tt11.FNumber like " + "'%" + FFullNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			num++;
			sql += " and t11.Fname like " + "'%" + FItemName + "%'";
		}
		if (StringUtil.isNotEmpty(FItemModel))
		{
			num++;
			sql += " and t11.FModel = " + "'" + FItemModel + "'";
		}

		if (StringUtil.isNotEmpty(FDeptIDName))
		{
			num++;
			sql += " and t4.FName  = " + "'" + FDeptIDName + "'";
		}

		// 排序
		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				sql += " order by " + orderField + dataGrid.getOrder();
			}
		}
		
		List<Map<String, Object>> maplist = null;

		try
		{
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("Fdate");
				arr[1] = (String) map.get("FBillNo");
				arr[2] = (String) map.get("FFullNumber");
				arr[3] = (String) map.get("FItemName");
				arr[4] = (String) map.get("FItemModel");
				arr[5] = (String) map.get("FUnitIDName");
				arr[6] =  String.valueOf((BigDecimal) map.get("Fauxqty"));
				arr[7] = (String) map.get("FDeptIDName");
				arr[8] = (String) map.get("FFetchTime");
				arr[9] = (String) map.get("FUse");
				arr[10] =  (String) map.get("FuserName");
				list.add(arr);
				 
			}
		
	
	
			String[] columnNames = new String[] { "日期", "单据编号","物料代码","物料名称","规格型号","单位","数量","请购部门","交货日期","用途","制单人"};
	       String titleName = "采购申请单.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

	        ExcelUtils.create(request, list, columnNames, titleName, fOut);
			
		} catch (Exception ex)
		{

		}
		
	
	}

}
